﻿///#undef MySql
///#define MSSql
#define MySql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using System.IO;
using System.Text;

using MySql.Data.MySqlClient;

namespace ukeasbjWeb
{
    public partial class CheckedCashFlow : WebList_SXD //System.Web.UI.Page
    {

        /// <summary>
        /// 网页调用时，读出前一次页的 search 条件，设定相应的控件。
        /// Session["Search_Leads"]) 在 Bt_Search_Click 函数中设定
        /// </summary>
        protected void Get_Search_Session()
        {
            string sSearch_CheckedCashFlow = Convert.ToString(Session["Search_CheckedCashFlow"]).Trim();
            if (sSearch_CheckedCashFlow != "")
            {
                string[] temp = sSearch_CheckedCashFlow.Split(',');

                Common_SXD.SetSelectedIndex(temp[0].Trim(), DDList_SearchKey);
                TextBox_Date_start_collector.Text = temp[1].Trim();
                TextBox_Date_end_collector.Text = temp[2].Trim();
                TextBox_SearchValue.Text = temp[3].Trim();
                TextMax_Lines.Text = temp[4].Trim();

                Common_SXD.SetSelected_RadioButtonList(temp[5].Trim(), RadioButton_OrderBy);

            }

        }

        /// <summary>
        /// 生成 sql 查询语句的 order by 部分
        /// </summary>
        /// <returns>返回Order by 字符串，如果出错，返回一个空格。</returns>
        protected string Get_OrderBy_FromSession(string myOrder_by)
        {
            string sOrder_by_CheckedCashFlow = Convert.ToString(Session["Order_by_CheckedCashFlow"]).Trim();
            if (sOrder_by_CheckedCashFlow != "")
                return "order by " + sOrder_by_CheckedCashFlow + " ";
            else
                return "order by " + myOrder_by + " ";

        }


        private static string static_sOrder_by_CheckedCashFlow;

        protected void Page_Load(object sender, EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
            Common_SXD.Is_LongIn(Response, Session, Request);

            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Collector = Convert.ToBoolean(Session["Is_Collector"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);
            ///bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);

            switch (Session["Office"].ToString().Trim())
            {
                // 开放顾问、文案权限的办公室 
                case "上海":
                case "BD":
                case "易申网":
                case "北京":
                case "南京":
                case "杭州":
                case "宁波":
                case "武汉":
                case "重庆":
                case "成都":
                case "广州":
                case "青岛":
                case "西安":
                    if (!bIs_O_M && !bIs_Admin && !bIs_Collector && !bIs_Accountant && 
                        !bIs_Writer && !bIs_Counsellor)
                    { // 判断当前用户是否为管理员
                        Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                        Response.Write("<br>对不起，只有顾问、文案、O. M.、收款员和总部会计才能使用该功能。");
                        Response.Write("</fong></h2>");
                        Response.End();
                    }

                    break;

                // 关闭顾问、文案权限
/*                case "南京":
                case "杭州":
                case "宁波":
                case "武汉":
                case "重庆":
                case "成都":
                case "广州":
                case "青岛":
                    if (!bIs_O_M && !bIs_Admin && !bIs_Collector && !bIs_Accountant && !bIs_Receptionist)
                    {
                        Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                        Response.Write("<br>对不起，只有O. M.、前台、收款员和会计才能使用该功能。");
                        Response.Write("</fong></h2>");
                        Response.End();
                    }
                    break;
*/
                default:
                    break;

             }

            ///////////// 读出网页调用时带入的 order by信息，设定 ASC DESC 后，存入session["Order_by_CheckedCashFlow"]
            ///存入 order by 到 session 之中
            static_sOrder_by_CheckedCashFlow = Request["Order_by_CheckedCashFlow"];
            string sLast_Order_by_Leads = Convert.ToString(Session["Order_by_CheckedCashFlow"]).Trim();
            if (static_sOrder_by_CheckedCashFlow == "" || static_sOrder_by_CheckedCashFlow == null) ///没有order by 信息输入
            {
                Session["Order_by_CheckedCashFlow"] = "";
            }
            else
            {
                if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "ASC")
                    Session["Order_by_CheckedCashFlow"] = static_sOrder_by_CheckedCashFlow + " ASC";
                else if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "DESC")
                    Session["Order_by_CheckedCashFlow"] = static_sOrder_by_CheckedCashFlow + " DESC";
                else
                    Session["Order_by_CheckedCashFlow"] = "";

            }

 
            if (!IsPostBack)
            {
                Common_SXD.Kill_Sleep_Threads(Response);
                // 初始化页面
                Common_SXD.Init_Searchkey(DDList_SearchKey, "已确认资金流向表");
                Init_Term_Search();
                Get_Search_Session();

                Init_Table_Contract();
                Textbox_LockDay_LastMonth.Text = Common_SXD.get_LockDay_LastMonth(Textbox_LockDay_LastMonth);
                Textbox_LockDay_LastMonth.Enabled = false;

                if (CheckBox_Display_Detailed.Checked == true)
                { ///显示详细数据时
                    Common_SXD.Init_Table_Bonus(Table_Bonus, Session["Office"].ToString().Trim());
                    Common_SXD.Init_Table_Bonus_US(Table_Bonus_US, Session["Office"].ToString().Trim());
                    Common_SXD.Init_Table_Bonus_100826(Table_Bonus_100826, Session["Office"].ToString().Trim());
                }
                Common_SXD.Init_Table_Bonus_101217(Table_Bonus_101217, Session["Office"].ToString().Trim());
                Common_SXD.Init_Table_Merit_Pay_140418(Table_Merit_Pay_140418, Session["Office"].ToString().Trim());
                Common_SXD.Init_Table_Counsellor_Bonus_130926(Table_Counsellor_Bonus_130926, Session["Office"].ToString().Trim());
                Common_SXD.Init_Table_Bonus_US_T_110315(Table_Bonus_US_T_110315, Session["Office"].ToString().Trim());
                
            }

        }


        private void Init_Table_Contract()
        {
            //  在此处放置用户代码以初始化页面
            //////////////////////////////////////////////////////
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Collector = Convert.ToBoolean(Session["Is_Collector"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);

            string sSearchKey = DDList_SearchKey.SelectedItem.Text; //查询条件
            string sSearchValue = TextBox_SearchValue.Text.Trim();

            Session.Timeout = 118;
            Date_Msg.Text = "收(退)费日期:";
            string sSql;
            sSql = Common_SXD.Search_Contract(sSearchKey, Session["Office"].ToString().Trim(),
                TextBox_Date_start_collector.Text, TextBox_Date_end_collector.Text, sSearchValue, Session, Response, 
                CheckBox_Display_Detailed, TextMax_Lines.Text, Date_Msg, null);


            /// 获取order by左侧存入sql中，order by右侧sOrder中。
            string[] strArray = Regex.Split(sSql, "order");
            sSql = strArray[0];
            string sOrder = strArray[1];
            strArray = Regex.Split(sOrder, "by");
            sOrder = strArray[1];

#if MSSql
            ///打开数据库，获取sqlReader变量
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql + Get_OrderBy_FromSession(sOrder),
                sqlCnn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

#elif MySql
            ///SqlConnection sqlCnn = new SqlConnection();
            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql + Get_OrderBy_FromSession(sOrder) + Limit_MySql(TextMax_Lines.Text),
                MySqlconn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = MysqlReader.FieldCount;

#endif

            //清除表中的内容
            Label_Total.Text = "";
            Label_Total_First_Payment.Text = "";
            Label_Total_Second_Payment.Text = "";
            Label_Total_Termination_Refund.Text = "";
            Table_Contract.Rows.Clear();
#if MSSql
            //记录个数
            string str = Common_SXD.CHAR_MSSQL_PLUS1("select count(Series_No) from Contract",
                Common_SXD.Filter_Search_Contract(sSearchKey, Session["Office"].ToString().Trim(), TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue, Session, Response, Date_Msg), Response);
#elif MySql
            //记录个数
            string str = Common_SXD.CHAR_MySQL_PLUS1("select count(Series_No) from Contract",
                Common_SXD.Filter_Search_Contract(sSearchKey, Session["Office"].ToString().Trim(), TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue, Session, Response, Date_Msg), Response);

#endif
            Common_SXD.Disp_Search_Contract(sSearchKey, Session["Office"].ToString().Trim(), TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue, Session,
                Label_Total, Label_Total_First_Payment,
                Label_Total_Second_Payment, Label_Total_Termination_Refund, Response, Date_Msg);


            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    ///string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    ///cell.Controls.Add(new LiteralControl(temp.Trim()));
                    ///row.Cells.Add(cell);
 ///////////////////////////////

                    string temp;
                    if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "DEFAULT")
                    {
#if MSSql
                        temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
#elif MySql
                        temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
#endif
                    }
                    else
                    {
#if MSSql
                        if (static_sOrder_by_CheckedCashFlow == sqlReader.GetName(i))
                        {
                            temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                        }
                        else
                        {
                            temp = "<A href=\"./CheckedCashFlow.aspx?Order_by_CheckedCashFlow=" + sqlReader.GetName(i) + "\">";
                            temp += Common_SXD.English_Chinese(sqlReader.GetName(i));
                            temp += "</A>";
                        }
#elif MySql
                        if (static_sOrder_by_CheckedCashFlow == MysqlReader.GetName(i))
                        {
                            temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
                        }
                        else
                        {
                            temp = "<A href=\"./CheckedCashFlow.aspx?Order_by_CheckedCashFlow=" + MysqlReader.GetName(i) + "\">";
                            temp += Common_SXD.English_Chinese(MysqlReader.GetName(i));
                            temp += "</A>";
                        }

#endif
                    }
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                
                
                
                }
                if (bIs_O_M || bIs_Collector || bIs_Counsellor || bIs_Writer)
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("修改"));
                    row.Cells.Add(cell);
                }
                Table_Contract.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            int j = 0;
#if MSSql
            while (sqlReader.Read())
#elif MySql
            while (MysqlReader.Read())
#endif
            {
                HtmlTableRow row = new HtmlTableRow();
#if MSSql
                String sNo_Series = sqlReader.GetString(0);
#elif MySql
                String sNo_Series = MysqlReader.GetString(0);
#endif
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                // Iterate through the cells of a row.
                {
                    HtmlTableCell cell = new HtmlTableCell();


                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=ReadOnly:" +
                        sNo_Series.Trim() +
                        "',900,620);\">只读" +
                        sNo_Series.Trim() +
                        "</a>"));
                    row.Cells.Add(cell);

                }

                for (int i = 1; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
#elif MySql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
#endif
                    row.Cells.Add(cell);
                }
#if MSSql
                string sNotes = Common_SXD.TimeToYMD(sqlReader.GetValue(nCol - 1).ToString());
#elif MySql
                string sNotes = Common_SXD.TimeToYMD(MysqlReader.GetValue(nCol - 1).ToString());
#endif
                int intTemp = sNotes.IndexOfAny(new Char[] { '上', '海' });

                if (intTemp != -1)
                {
                    sNotes = Common_SXD.TimeToYMD(sNotes);
                }

                if (bIs_O_M || bIs_Collector || bIs_Counsellor || bIs_Writer)
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    //				if(bIs_Collector)
                    //				{//////只有收款员、顾问、营运经理、文案能修改数据
                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=EditContractSecondPay:" +
                        sNo_Series.Trim() +
                        "',900,620);\">二期款/执行情况</a>"));
                    row.Cells.Add(cell);
                    //				}
                }

                Table_Contract.Rows.Add(row);
                j++;
            }
#if MSSql
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接
            
#endif
            
        }



        /// <summary>
        /// 设定合同查询的过滤条件 2
        /// </summary>
        /// <param name="sSearchKey"></param>
        /// <param name="sOffice"></param>
        /// <param name="sStart_Date"></param>
        /// <param name="sEnd_Date"></param>
        /// <param name="sSearchValue"></param>
        /// <returns></returns>
        private string Filter_Search_Contract_2(string sSearchKey, string sOffice, string sStart_Date, string sEnd_Date, string sSearchValue)
        {
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Collector = Convert.ToBoolean(Session["Is_Collector"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);

            string sSql = "";

            //零中介退款，扣奖金的名单
            if (sSearchKey == "零中介退款")
            {
                sSql = Common_SXD.sSql_IsOffice_ZongBu(sOffice, sSql);
                if ((bIs_Counsellor || bIs_Writer))
                    if (!bIs_O_M)
                        if (!bIs_Admin)
                            if (!bIs_Collector)
                                /*if (!bIs_Receptionist)*/
                                if (Session["Email"].ToString().Trim().ToLower() != "sharon_li") /// 北京李英美，可以看北京办公室的已确认合同
                                    sSql += "( Counsellor = '" + Session["C_N_Employee"] + "' or  Writer = '" + Session["C_N_Employee"] + "' ) and ";

                // 退款日期等条件
                sSql += "Termination_Refund<>0 and Student_Type = '零中介' and ((Date_Termination_Refund >= '" + sStart_Date + "' ) AND (Date_Termination_Refund <= '" + TextBox_Date_end_collector.Text + "' )) AND (notes like '%deduct%' ) and ";

                sSql += "(  Student_Name  like '%" + sSearchValue + "%' ";
                sSql += "or  Series_No  like '%" + sSearchValue + "%' ";
                sSql += "or  Notes  like '%" + sSearchValue + "%' ";
                sSql += "or  Remitter  like '%" + sSearchValue + "%' ";
                sSql += "or  Ref_Cash_No  like '%" + sSearchValue + "%' ";
                sSql += "or  Pinyi  like '%" + sSearchValue + "%' ";
                sSql += "or  No_Contract  like '%" + sSearchValue + "%' )";


            }

            return sSql;
        }

 

        private string Sort_Search_Contract(string sSearchKey, string sOffice, string sStart_Date, string sEnd_Date, string sSearchValue)
        {
            if (sSearchKey == "零中介退款")
            {

                //				sSql += " order by Office,Counsellor, Series_No DESC";
                return "Office,Counsellor, Series_No DESC";

            }
            else if (sSearchKey == "客户退款")
            {
                //				sSql += " order by  Office, Counsellor, Student_Type, Series_No DESC";
                return "Office, Counsellor, Student_Type, Series_No DESC";

            }
            else if (sSearchKey == "合同原始编号")
            {
                //				sSql += " order by  Office, Counsellor, Student_Type, Series_No DESC";
                return "Office, No_Origin, Series_No DESC";

            }
            else // 除去两种退款以外的情况
            {
                return "Office, Counsellor, Student_Type, Series_No DESC";
            }

        }

        private void Init_Term_Search()
        {
            TextBox_Date_start_collector.Text = Common_SXD.startDate_CurrentMonth();
            TextBox_Date_end_collector.Text = Common_SXD.TimeToYMD(DateTime.Now.ToString());
            TextMax_Lines.Text = "86";

        }

        protected void Bt_Search_Click(object sender, EventArgs e)
        {
            if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "DEFAULT")
                static_sOrder_by_CheckedCashFlow = "";

            ///保存 session["Search_CheckedCashFlow"]
            ///
            string sSearch_CheckedCashFlow = "";
            sSearch_CheckedCashFlow += DDList_SearchKey.SelectedItem.Text.Trim() + ", ";
            sSearch_CheckedCashFlow += TextBox_Date_start_collector.Text.Trim() + ", ";
            sSearch_CheckedCashFlow += TextBox_Date_end_collector.Text.Trim() + ", ";
            sSearch_CheckedCashFlow += TextBox_SearchValue.Text.Trim() + ", ";
            sSearch_CheckedCashFlow += TextMax_Lines.Text.Trim() + ", ";
            sSearch_CheckedCashFlow += RadioButton_OrderBy.SelectedItem.Text.ToString().Trim() + ", ";

            Session["Search_CheckedCashFlow"] = sSearch_CheckedCashFlow;
 

            Init_Table_Contract();
            if (CheckBox_Display_Detailed.Checked == true)
            { ///显示详细数据时
                Common_SXD.Init_Table_Bonus(Table_Bonus, Session["Office"].ToString().Trim());
                Common_SXD.Init_Table_Bonus_US(Table_Bonus_US, Session["Office"].ToString().Trim());
                Common_SXD.Init_Table_Bonus_100826(Table_Bonus_100826, Session["Office"].ToString().Trim());
            }
            Common_SXD.Init_Table_Bonus_101217(Table_Bonus_101217, Session["Office"].ToString().Trim());
            Common_SXD.Init_Table_Merit_Pay_140418(Table_Merit_Pay_140418, Session["Office"].ToString().Trim());
            Common_SXD.Init_Table_Counsellor_Bonus_130926(Table_Counsellor_Bonus_130926, Session["Office"].ToString().Trim());
            Common_SXD.Init_Table_Bonus_US_T_110315(Table_Bonus_US_T_110315, Session["Office"].ToString().Trim());

        }


 



    }
}
